﻿using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using System;
using System.Data;
using System.Security.Cryptography;
using System.Text;

namespace DataSync.Core
{
    public class DataBaseInfoService
    {
        public DataBaseInfoService()
        {
        }

        /// <summary>
        /// 数据Copy
        /// </summary>
        /// <param name="conSourceStr">源库连接串</param>
        /// <param name="conTargetStr">目标库连接串</param>
        /// <param name="tabStr">表名</param>
        public static void InsertTable(string conSourceStr, string conTargetStr, string tabStr)
        {
            SqlConnection connSource = new SqlConnection();
            connSource.ConnectionString = conSourceStr;
            connSource.Open();

            SqlConnection connTarget = new SqlConnection();
            connTarget.ConnectionString = conTargetStr;
            connTarget.Open();

            SqlDataAdapter adapter1 = new SqlDataAdapter("select * from " + tabStr, connSource);
            DataSet dataSet1 = new DataSet();

            if (dataSet1 != null)
            {
                adapter1.Fill(dataSet1, tabStr);
            }
            SqlDataAdapter adapter2 = new SqlDataAdapter("select * from " + tabStr, connTarget);
            DataSet dataSet2 = new DataSet();

            SqlCommand cmd2 = new SqlCommand("select count(*) from " + tabStr, connTarget);
            Object res2 = cmd2.ExecuteScalar();

            if (res2 != null)
            {
                int nCount = Convert.ToInt32(res2.ToString());
                if (nCount == 0)
                {
                    connTarget.Close();
                    connSource.Close();
                    return;
                }
            }
            if (dataSet2 != null)
            {
                adapter2.Fill(dataSet2, tabStr);
            }
            //复制数据
            for (int j = 0; j < dataSet2.Tables[0].Rows.Count; j++)
            {
                dataSet1.Tables[0].LoadDataRow(dataSet2.Tables[0].Rows[j].ItemArray, false);
            }
            //将DataSet变换显示在与其关联的目标数据库
            SqlCommandBuilder cb = new SqlCommandBuilder(adapter1);
            adapter1.Update(dataSet1, tabStr);
            cb.RefreshSchema();
            Console.WriteLine("表" + tabStr + "复制成功！");
            connTarget.Close();
            connSource.Close();
        }
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="dataTable"></param>
        public static void DataBulkCopy(SqlConnection conn, string tableName, DataTable dataTable)
        {
            conn.Open();
            var sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null);
            
            sqlbulkcopy.BulkCopyTimeout = 100;  //超时之前操作完成所允许的秒数
            sqlbulkcopy.BatchSize = dataTable.Rows.Count;  //每一批次中的行数
            sqlbulkcopy.DestinationTableName = tableName;  //服务器上目标表的名称
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                sqlbulkcopy.ColumnMappings.Add(i, i);  //映射定义数据源中的列和目标表中的列之间的关系
            }
            sqlbulkcopy.WriteToServer(dataTable);  // 将DataTable数据上传到数据表中
            conn.Close();
        }

        /// <summary>
        /// 给每一列增加唯一值
        /// </summary>
        /// <param name="dataTable"></param>
        public static void DataTableAddColumsMd5(DataTable dataTable)
        {
            // 定义要添加的列名称及数据类型
            string columnName = "MD5"; // 新列的名称
            Type columnType = typeof(string);   // 新列的数据类型（这里示例为 int）

            // 向 DataTable 中添加新列
            dataTable.Columns.Add(columnName, columnType);

            // 遍历已有行，将每行的新列都设置为指定的默认值
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                var strbuild = new StringBuilder();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    strbuild.Append(dataTable.Rows[i][j].ToString() + ",");
                }

                string jsonString = JsonConvert.SerializeObject(strbuild.ToString().TrimEnd(',', ' '));

                using (var md5Hasher = MD5.Create())
                {
                    byte[] hashBytes = md5Hasher.ComputeHash(System.Text.Encoding.UTF8.GetBytes(jsonString));

                    StringBuilder sb = new StringBuilder();
                    for (int k = 0; k < hashBytes.Length; k++)
                    {
                        sb.Append(hashBytes[k].ToString("x2"));
                    }

                    dataTable.Rows[i][columnName] = sb;
                }

            }
        }

        /// <summary>
        /// 给datatable增加一列
        /// </summary>
        /// <param name="dataTable"></param>
        public static void DatatableAddColumns(DataTable dataTable)
        {
            // 定义要添加的列名称及数据类型
            string columnName = "IsSync"; // 新列的名称
            Type columnType = typeof(bool);   // 新列的数据类型（这里示例为 int）

            // 向 DataTable 中添加新列
            dataTable.Columns.Add(columnName, columnType);

            // 遍历已有行，将每行的新列都设置为指定的默认值
            foreach (DataRow row in dataTable.Rows)
            {
                row[columnName] = 0; // 设置新列的默认值
            }
        }


        /// <summary>
        /// 给datatable删除列
        /// </summary>
        /// <param name="dataTable"></param>
        public static void DatatableRemoveCloumns(DataTable dataTable)
        {
            // 定义要删除的列名称及数据类型
            string columnName = "IsSync";
            // 向 DataTable 中移除新列
            if (CheckColumnExistence(dataTable, columnName))
            {
                dataTable.Columns.Remove(columnName);
            }
            string columnNameByMD5 = "MD5";
            if (CheckColumnExistence(dataTable, columnNameByMD5))
            {
                //移除列
                dataTable.Columns.Remove(columnNameByMD5);
            }
        }

        public static bool CheckColumnExistence(DataTable table, string columnName)
        {
            foreach (DataColumn column in table.Columns)
            {
                if (string.Equals(column.ColumnName, columnName))
                    return true;
            }

            return false;
        }
        /// <summary>
        /// 通过sql传入的type创建表类型
        /// </summary>
        /// <param name="dataType"></param>
        /// <returns></returns>
        public static string GetSqlDataType(Type dataType)
        {
            if (dataType == typeof(int))
            {
                return "INT";
            }
            else if (dataType == typeof(string))
            {
                return "NVARCHAR(255)";
            }
            else if (dataType == typeof(bool))
            {
                return "bit";
            }
            else if (dataType == typeof(short))
            {
                return "smallint";
            }
            else if (dataType == typeof(byte))
            {
                return "tinyint";
            }
            else if (dataType == typeof(long))
            {
                return "bigint";
            }
            else if (dataType == typeof(double))
            {
                return "float";
            }
            else if (dataType == typeof(decimal))
            {
                return "decimal";
            }
            else if (dataType == typeof(DateTime))
            {
                return "datetime";
            }
            else if (dataType == typeof(Guid))
            {
                return "uniqueidentifier";
            }
            else
            {
                // 其他数据类型的映射规则

                return "UNKNOWN";
            }
        }
    }
}